
ALTER             function fn_saleshold_orders_display(@salesorder_no varchar(20), @isSample int)
returns  @fn_result Table(SalesOrder_ID int,
                          SO_Customer_PO_Number varchar(50),
                          Customer_Id int,
			  LineItem_Id int,
			COOL_Line_Item_No int,
			Product_ID int,
			-- Sharath on 4th July 03.
 			COOL_Description varchar(500),
			-- Ends Here.
			COOL_Qty int,
                        COOL_Unit_Cost float,
                        COOL_Amount float,
                        COOL_Requested_Date datetime,
			COOL_Sugg_Schedule_Date_With_Substitutables datetime,
                        COOL_Sugg_Schedule_Date_Without_Substitutables datetime,
                        COOL_Booking_Date datetime,
			COOL_Schedule_Date datetime,
			COOL_Revised_Date datetime,
                        COOL_Status varchar(50),
                        COOL_Taxable int,
                        COOL_Is_Cancel int,
                        SalesOrder_No varchar(50),
                        Final_Cool_Status varchar(50),
			Customer_PartNumber varchar(50),
			SO_Booking_Date datetime, -- Prabhanjan (11 Mar 2004)
			SO_ODM_Id int,
			SHIPPING_TERMS VARCHAR(50))
as
begin
	declare @sales_order_id int
	declare @sales_customerpo varchar(50)
        declare @customerid int
	declare @line_item_id int
        declare @cool_line_item_no int
	declare @temp_product_id int
	-- ** Sharath on 4th July 03.
	declare @temp_cool_description varchar(500)
	-- ** Ends Here.
	declare @temp_cool_qty int
	declare @temp_cool_unit_cost float
	declare @temp_cool_amount float
        declare @temp_cool_requested_date datetime
	declare @temp_cool_sugg_date_with_substitutables datetime
	declare @temp_cool_sugg_date_without_substitutables datetime
	declare @temp_cool_booking_date datetime
	declare @temp_cool_schedule_date datetime
	declare @temp_cool_revised_date datetime
	declare @temp_cool_status varchar(50)
	declare @temp_cool_taxable int
        declare @sales_order_no varchar(50)
	declare @temp_cool_is_cancel int
        declare @final_cool_status varchar(50)
        declare @cslit_status varchar(50)
	declare @simplecount int
	declare @customer_partnumber varchar(50)

	declare @so_booking_date datetime -- Prabhanjan (11 Mar 2004)

	declare @end_user varchar(50)
	declare @end_user_id int
	declare @odm_id int
	DECLARE @SHIPPING_TERMS VARCHAR(50)

	declare @open_orders_cursor CURSOR
	declare @approved_orders_cursor CURSOR
        declare @status_cursor CURSOR
	declare @result Table(SalesOrder_ID int,
			SO_Customer_PO_Number varchar(50),
                        Customer_Id int,
			LineItem_Id int,
			COOL_Line_Item_No int,
			Product_ID int,
			-- ** Sharath on 4th Jult 03.
			COOL_Description varchar(500),
			-- ** Ends Here.
			COOL_Qty int,
			
                        COOL_Unit_Cost float,
                        COOL_Amount float,
                        COOL_Requested_Date datetime,
			COOL_Sugg_Schedule_Date_With_Substitutables datetime,
                        COOL_Sugg_Schedule_Date_Without_Substitutables datetime,
                        COOL_Booking_Date datetime,
			COOL_Schedule_Date datetime,
			COOL_Revised_Date datetime,
                        COOL_Status varchar(50),
                        COOL_Taxable int,
                        COOL_Is_Cancel int,
                        SalesOrder_No varchar(50),
                        Final_Cool_Status varchar(50),
			Customer_PartNumber varchar(50),
			SO_Booking_Date datetime, -- Prabhanjan (11 Mar 2004)
			SO_ODM_Id int,
			SHIPPING_TERMS VARCHAR(50))

--SELECT @sales_customerpo = Sales_Order.SO_Customer_PO_Number ,@customerid = Sales_Order.Customer_Id FROM Sales_Order WHERE SalesOrder_Id like @salesorder_id
SET @open_orders_cursor = CURSOR FOR 
    SELECT Sales_Order.SalesOrder_ID ,Sales_Order.SO_Customer_PO_Number,Sales_Order.Customer_Id,LineItem_Id ,COOL_Line_Item_No ,Product_ID ,COOL_Description ,COOL_Qty ,COOL_Unit_Cost ,
COOL_Amount,COOL_Requested_Date ,COOL_Sugg_Schedule_Date_With_Substitutables ,
COOL_Sugg_Schedule_Date_Without_Substitutables ,COOL_Booking_Date,COOL_Schedule_Date ,COOL_Revised_Date ,
COOL_Status ,COOL_Taxable,COOL_Is_Cancel , COOL_CUSTOMER_PARTNUMBER, Sales_Order.SalesOrder_No,Sales_Order.SO_Booking_Date, Sales_Order.SO_EndUser, Sales_Order.SO_ODM_Id, Sales_Order.SHIPPING_TERMS
FROM Customer_Open_Order_LineItem ,Sales_Order WHERE Customer_Open_Order_LineItem.SalesOrder_Id in (select salesorder_id from sales_order where salesOrder_no like @salesorder_no and SO_Is_Sample = @isSample)
 and  Customer_Open_Order_LineItem.SalesOrder_Id = Sales_Order.SalesOrder_Id  and COOL_Status<> 'APPROVED'

SET @approved_orders_cursor = CURSOR FOR 
    SELECT Sales_Order.SalesOrder_ID ,Sales_Order.SO_Customer_PO_Number,Sales_Order.Customer_Id,LineItem_Id,COOL_Line_Item_No ,Product_ID ,COOL_Description, COOL_Qty ,COOL_Unit_Cost ,
COOL_Amount ,COOL_Requested_Date ,COOL_Sugg_Schedule_Date_With_Substitutables,
COOL_Sugg_Schedule_Date_Without_Substitutables ,COOL_Booking_Date,COOL_Schedule_Date ,COOL_Revised_Date ,
COOL_Status,COOL_Taxable ,COOL_Is_Cancel, COOL_CUSTOMER_PARTNUMBER, Sales_Order.SalesOrder_No, Sales_Order.SO_Booking_Date datetime, Sales_Order.SO_EndUser,  Sales_Order.SO_ODM_Id, Sales_Order.SHIPPING_TERMS
FROM Customer_Open_Order_LineItem,Sales_Order WHERE Customer_Open_Order_LineItem.SalesOrder_Id in (select salesorder_id from sales_order where salesOrder_no like @salesorder_no and SO_Is_Sample = @isSample)
and Customer_Open_Order_LineItem.SalesOrder_Id = Sales_Order.SalesOrder_Id and COOL_Status = 'APPROVED'

	open @open_orders_cursor		
		fetch next from @open_orders_cursor into @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
	@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,
	@temp_cool_schedule_date ,@temp_cool_revised_date, @temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel, @customer_partnumber,
	@sales_order_no, @so_booking_date, @end_user, @odm_id, @SHIPPING_TERMS
	while @@fetch_status = 0
	begin
	--Added by Sarada on 20th Jan 2004 to get Customer partNumber
	/*set @customer_partnumber = null

		-- prabhanjan (22 Mar 2004)		
		if(ltrim(rtrim(@end_user)) = '')
		begin	
			select @customer_partnumber = Customer_PartNumber from Customer_PartNumbers where Product_id = @temp_product_id and Customer_ID = @customerid and Odm_id is NULL
		end
		else
		begin
			SELECT @end_user_id = End_User_ID from End_User_Table where E_Name = @end_user
			select @customer_partnumber = Customer_PartNumber from End_User_Part_Numbers where Product_ID = @temp_product_id and End_User_ID = @end_user_id
		end

		--SELECT @customer_partnumber = Customer_PartNumber from Customer_PartNumbers where Product_Id = @temp_product_id and Customer_Id = @customerid		 

  	 */
         if(  @temp_cool_status = 'CANCELLED' ) --if4
	         begin
	        insert  @result values ( @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
				@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
				@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,@temp_cool_schedule_date ,@temp_cool_revised_date,
				@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,'CANCELLED',@customer_partnumber, @so_booking_date, @odm_id, @SHIPPING_TERMS)
	
			        fetch next from @open_orders_cursor into @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
			@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,
			@temp_cool_schedule_date ,@temp_cool_revised_date, @temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel, @customer_partnumber,
			@sales_order_no, @so_booking_date, @end_user, @odm_id, @SHIPPING_TERMS	
			--SELECT @customer_partnumber = Customer_PartNumber from Customer_PartNumbers where Product_Id = @temp_product_id and Customer_Id = @customerid		 	
	         end --if4
 
         if(  @temp_cool_status = 'SHIPPED' ) --if1
         begin
        insert  @result values ( @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
			@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
			@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,@temp_cool_schedule_date ,@temp_cool_revised_date,
			@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,'SHIPPED',@customer_partnumber, @so_booking_date, @odm_id, @SHIPPING_TERMS)
		        fetch next from @open_orders_cursor into @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
		@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,
		@temp_cool_schedule_date ,@temp_cool_revised_date, @temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel, @customer_partnumber,
		@sales_order_no, @so_booking_date, @end_user, @odm_id, @SHIPPING_TERMS
		--SELECT @customer_partnumber = Customer_PartNumber from Customer_PartNumbers where Product_Id = @temp_product_id and Customer_Id = @customerid		 
        end --if1
        
         if(  @temp_cool_status = 'HOLD' ) --if2
         begin
        insert  @result values ( @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
			@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
			@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,@temp_cool_schedule_date ,@temp_cool_revised_date,
			@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,'HOLD',@customer_partnumber, @so_booking_date, @odm_id, @SHIPPING_TERMS)
		        fetch next from @open_orders_cursor into @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
		@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,
		@temp_cool_schedule_date ,@temp_cool_revised_date, @temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel, @customer_partnumber,
		@sales_order_no, @so_booking_date, @end_user, @odm_id, @SHIPPING_TERMS
		--SELECT @customer_partnumber = Customer_PartNumber from Customer_PartNumbers where Product_Id = @temp_product_id and Customer_Id = @customerid		 	
         end --if2
	  if(  @temp_cool_status = 'PENDING' ) --if3
	         begin
	        insert  @result values ( @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
				@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
				@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,@temp_cool_schedule_date ,@temp_cool_revised_date,
				@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,'PENDING',@customer_partnumber, @so_booking_date, @odm_id, @SHIPPING_TERMS)
	
			        fetch next from @open_orders_cursor into @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
			@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,
			@temp_cool_schedule_date ,@temp_cool_revised_date, @temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel, @customer_partnumber,
			@sales_order_no, @so_booking_date, @end_user, @odm_id, @SHIPPING_TERMS
			--SELECT @customer_partnumber = Customer_PartNumber from Customer_PartNumbers where Product_Id = @temp_product_id and Customer_Id = @customerid		 		
	         end --if3
      end --while

       open @approved_orders_cursor		
			fetch next from @approved_orders_cursor into @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
		@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,
		@temp_cool_schedule_date ,@temp_cool_revised_date, @temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel, @customer_partnumber,
		@sales_order_no, @so_booking_date, @end_user, @odm_id, @SHIPPING_TERMS
		
	while @@fetch_status = 0
	begin

	--Added by Sarada on 20th Jan 2004 to get Customer partNumber
	/*set @customer_partnumber = null

	-- prabhanjan (22 Mar 2004)		
	if(ltrim(rtrim(@end_user)) = '')
	begin	
		select @customer_partnumber = Customer_PartNumber from Customer_PartNumbers where Product_id = @temp_product_id and Customer_ID = @customerid
	end
	else
	begin
		SELECT @end_user_id = End_User_ID from End_User_Table where E_Name = @end_user
		select @customer_partnumber = Customer_PartNumber from End_User_Part_Numbers where Product_ID = @temp_product_id and End_User_ID = @end_user_id
	end
	*/
	--SELECT @customer_partnumber = Customer_PartNumber from Customer_PartNumbers where Product_Id = @temp_product_id and Customer_Id = @customerid		 

        declare @cslit_status1 varchar(50)
	/* Modified By Basha inorder to get status for a line item */
       
	   SET @status_cursor = CURSOR FOR 
	   SELECT distinct cslit_status FROM Customer_Shipping_Line_Item_Table WHERE LineItem_Id = @line_item_id and cslit_status =(select max(cslit_status) from customer_shipping_line_item_table where lineitem_id = @line_item_id )
	   --SELECT cslit_status FROM Customer_Shipping_Line_Item_Table WHERE LineItem_Id = @line_item_id and cslit_id=(select max(cslit_id) from customer_shipping_line_item_table where lineitem_id = @line_item_id )
           --SELECT cslit_status FROM Customer_Shipping_Line_Item_Table WHERE LineItem_Id = @line_item_id
           declare @tempcount int
           select  @tempcount = count(cs.lineitem_id) from customer_shipping_line_item_table cs , customer_open_order_lineitem c 
           where c.cool_status ='APPROVED' and cs.lineitem_id = @line_item_id and cs.lineitem_id = c.lineitem_id
	       if( @tempcount = 0 )
	         begin
	         insert  @result values ( @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
				@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
				@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,@temp_cool_schedule_date ,@temp_cool_revised_date,
				@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,'APPROVED',@customer_partnumber, @so_booking_date, @odm_id, @SHIPPING_TERMS)
			
	         end
    
      else
      begin         open @status_cursor		
	fetch next from @status_cursor into @cslit_status1
        
	while @@fetch_status =0
	begin
	
        if( @cslit_status1 = 'ISSUED' )
          begin
          insert  @result values ( @sales_order_id,@sales_customerpo,@customerid,@line_item_id ,  @cool_line_item_no,@temp_product_id, @temp_cool_description, @temp_cool_qty,
			@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
			@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,@temp_cool_schedule_date ,@temp_cool_revised_date,
			@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,'ISSUED',@customer_partnumber, @so_booking_date, @odm_id, @SHIPPING_TERMS)
       end
       if( @cslit_status1 = 'CANCELLED' )
         begin
         insert  @result values ( @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
			@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
			@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,@temp_cool_schedule_date ,@temp_cool_revised_date,
			@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,'APPROVED',@customer_partnumber, @so_booking_date, @odm_id, @SHIPPING_TERMS)
		        
       end
       if( @cslit_status1 = 'SHIPPED' )
         begin
         insert  @result values ( @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
			@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,
			@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,@temp_cool_schedule_date ,@temp_cool_revised_date,
			@temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel,@sales_order_no,'PARTIALLY SHIPPED',@customer_partnumber, @so_booking_date, @odm_id, @SHIPPING_TERMS)
	break
       end
         fetch next from @status_cursor into @cslit_status1  
       end --status_cursor while
   	close @status_cursor
	deallocate @status_cursor
  end --else      
         fetch next from @approved_orders_cursor into @sales_order_id,@sales_customerpo,@customerid,@line_item_id , @cool_line_item_no, @temp_product_id, @temp_cool_description, @temp_cool_qty,
		@temp_cool_unit_cost,@temp_cool_amount,@temp_cool_requested_date,@temp_cool_sugg_date_with_substitutables,@temp_cool_sugg_date_without_substitutables,@temp_cool_booking_date,
		@temp_cool_schedule_date ,@temp_cool_revised_date, @temp_cool_status, @temp_cool_taxable,@temp_cool_is_cancel, @customer_partnumber,
		@sales_order_no, @so_booking_date, @end_user, @odm_id, @SHIPPING_TERMS
	--SELECT @customer_partnumber = Customer_PartNumber from Customer_PartNumbers where Product_Id = @temp_product_id and Customer_Id = @customerid		 		
      end	--approved_orders_cursor while
	
	close @approved_orders_cursor
	deallocate @approved_orders_cursor
    insert @fn_result select * from @result order by SalesOrder_ID,LineItem_Id
  return 
END




